package fun.rockchen.dao;

import fun.rockchen.bean.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author Rock Chen
 * @Description:
 * @date 2023/10/21 20:04
 */

@Mapper
public interface StatisticsDao {
    @Select("select type, SUM(CASE WHEN MONTH(startTime) = 1 THEN 1 ELSE 0 END) AS Jan," +
            "       SUM(CASE WHEN MONTH(startTime) = 2 THEN 1 ELSE 0 END) AS Feb," +
            "       SUM(CASE WHEN MONTH(startTime) = 3 THEN 1 ELSE 0 END) AS Mar," +
            "       SUM(CASE WHEN MONTH(startTime) = 4 THEN 1 ELSE 0 END) AS Apr," +
            "       SUM(CASE WHEN MONTH(startTime) = 5 THEN 1 ELSE 0 END) AS May," +
            "       SUM(CASE WHEN MONTH(startTime) = 6 THEN 1 ELSE 0 END) AS Jun," +
            "       SUM(CASE WHEN MONTH(startTime) = 7 THEN 1 ELSE 0 END) AS Jul," +
            "       SUM(CASE WHEN MONTH(startTime) = 8 THEN 1 ELSE 0 END) AS Aug," +
            "       SUM(CASE WHEN MONTH(startTime) = 9 THEN 1 ELSE 0 END) AS Sep," +
            "       SUM(CASE WHEN MONTH(startTime) = 10 THEN 1 ELSE 0 END) AS Oct," +
            "       SUM(CASE WHEN MONTH(startTime) = 11 THEN 1 ELSE 0 END) AS Nov," +
            "       SUM(CASE WHEN MONTH(startTime) = 12 THEN 1 ELSE 0 END) AS DECE" +
            " from task inner join userMapTask on task.id = userMapTask.taskId where userId = '${id}' group by type")
    List<TypeStatisticsEntity> getTypeList(String id);

    //    @Select("select type, count(*) as count from task inner join userMapTask on task.id = taskId where DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(DATE_FORMAT(startTime,'%Y-%m-%d')) and userId = '${id}' GROUP BY type; ")
    @Select("select distinct task.type as title, IFNULL(t.num, 0) as num " +
            "from task" +
            "    left join(" +
            "        select type as title, count(*) as num" +
            "            from task" +
            "                inner join userMapTask on task.id = taskId" +
            "            WHERE DATE_FORMAT( startTime, '%Y-%m-%d') BETWEEN DATE(NOW()) - INTERVAL 7 DAY AND DATE(NOW())" +
            "            and userId = '${id}'" +
            "            group by type" +
            ") t on task.type = t.title;")
    List<TypeDayEntity> getTypeListInSevenDay(String id);

    /**
     * 昨日 1
     * 今日 0
     */
    @Select("select distinct task.type as title, IFNULL(t.num, 0) as num " +
            "from task" +
            "    left join(" +
            "        select type as title, count(*) as num" +
            "            from task" +
            "                inner join userMapTask on task.id = taskId" +
            "            WHERE DATE_FORMAT( startTime, '%Y-%m-%d') like DATE_SUB(CURDATE(), INTERVAL ${day} DAY)" +
            "            and userId = '${id}'" +
            "            group by type" +
            ") t on task.type = t.title")
    List<TypeDayEntity> getTypeListDay(TypeDayParamEntity typeDayParamEntity);

    @Select("""
            select time, coalesce(SUM(total), 0) as total from (SELECT TIMESTAMPDIFF(SECOND, starttime, endtime) AS total, DATE_FORMAT(startTime,'%Y-%m-%d') as time
            FROM task
            inner join userMapTask uMT on task.id = uMT.taskId
            where startTime like concat('%',DATE_FORMAT('${time}','%Y-%m'),'%') and userId = '${userId}') as tsd group by time order by time;
            """)
    List<TaskTotalTimeEntity> getTaskTime(TaskTotalTimeParam taskTotalTimeParam);

    @Select("""
            select tagContent as title, count(*) as num from task
                inner join taskMapTag tMT on task.id = tMT.taskId
                inner join userMapTask uMT on task.id = uMT.taskId
                inner join tag t on tMT.tagId = t.id
            where userId = '${userId}'
            group by tagId;""")
    List<TagStatisticsEntity> getTag(String userId);
}
